package csv;

import java.io.FileWriter;
import java.io.IOException;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.opencsv.CSVWriter;


public class OracleToCSV {
	private static Connection con = null;

	public OracleToCSV() {
		if (con == null) {
			con = getConnection();
		}
	}

	private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
	private static final String URL = "jdbc:oracle:thin:@ (DESCRIPTION = (ADDRESS = (PROTOCOL =TCP)"
			+ "(HOST = 10.1.7.9)(PORT = 1521)) (LOAD_BALANCE = yes)(CONNECT_DATA = (SERVICE_NAME = tc) ) )";
	private static final String USERNAME = "dataexchange";
	private static final String PASSWORD = "dataexchange123";

	private static Connection getConnection() {
		try {
			Class.forName(DRIVER);
			if (con == null) {
				con = DriverManager.getConnection(URL, USERNAME, PASSWORD);
				con.setAutoCommit(false);
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return con;
	}


	public void getAccount() throws SQLException, IOException {
		String sql = "SELECT FTPACCOUNT,FTPPASSWORD,SCNAME FROM  CHYIII_DEC_TSUPPLIERCONTACT";
		PreparedStatement pst = con.prepareStatement(sql);
		StringBuffer bbb = new StringBuffer();
		ResultSet rs = pst.executeQuery();
		// StringWriter sw = new StringWriter();
		FileWriter sw = new FileWriter("d:/out.csv");
		CSVWriter writer = new CSVWriter(sw);
		writer.writeAll(rs, true);
		writer.close();
		sw.close();
		// System.out.println(sw);
		con.commit();
		pst.close();
		con.close();

	}

	public static void main(String[] args) throws SQLException {
		OracleToCSV t = new OracleToCSV();
		try {
			t.getAccount();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
